/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Choice;
import com.tces.model.Question;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class ChoiceDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(QuestionDAO.class);

    /**
     * This will retrieve all the choices that is visible base on the
     *
     * @param questionID
     * @param questionID
     * @return active choice list
     * @throws Exception
     */
    public static List<Choice> getActiveChoices(int questionID) throws Exception {
        List<Choice> choiceList = new ArrayList<Choice>();
        String sql = "SELECT\n"
                + "q.question_descrption,\n"
                + "q.question_id,\n"
                + "qc.questions_choices_order,\n"
                + "c.choice_id,\n"
                + "c.choice_value,\n"
                + "c.choice_description,\n"
                + "c.choice_numeric_value,\n"
                + "c.choice_visible\n"
                + "FROM\n"
                + "questions_choices AS qc\n"
                + "INNER JOIN choices AS c ON c.choice_id = qc.choice_id\n"
                + "INNER JOIN questions AS q ON qc.question_id = q.question_id \n"
                + "WHERE q.question_id=?\n"
                + "ORDER BY q.question_id, qc.questions_choices_order";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, questionID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Choice choice;
        while (rs.next()) {
            choice = new Choice();
            choice.setChoiceID(rs.getInt("choice_id"));
            choice.setChoiceValue(rs.getString("choice_value"));
            choice.setChoiceDescription(rs.getString("choice_description"));
            choice.setQuestionDescription(rs.getString("question_descrption"));
            choice.setQuestionID(rs.getInt("question_id"));
            choice.setChoiceVisible(rs.getInt("choice_visible"));
            choice.setQuestionsChoicesOrder(rs.getInt("questions_choices_order"));
            choice.setChoiceNumericValue(rs.getInt("choice_numeric_value"));
            choiceList.add(choice);
        }
        closeConnection(getConnectionObject());
        return choiceList;
    }
}
